package com.nit.lab.vote.mapper.vote;

import com.nit.lab.vote.dto.vote.CandidateDTO;
import com.nit.lab.vote.dto.vote.UPDTO;
import com.nit.lab.vote.dto.vote.VoteDTO;
import com.nit.lab.vote.entity.vote.VoteEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface VoteMapper {
    @Select("<script>Select *, vote.vote_id AS `key`, u.user_name AS `userName`, " +
            "c.candidate_name AS `candidateName`, p.project_name AS `projectName` from vote " +
            "JOIN user u ON u.user_id = vote.user_id " +
            "JOIN candidate c ON c.candidate_id = vote.candidate_id " +
            "JOIN project p ON p.project_id = vote.project_id " +
            "where true " +
            "<if test = 'projectName != null'> AND p.project_name LIKE CONCAT('%', #{projectName}, '%') </if>" +
            "<if test = 'userName != null'> AND u.user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'candidateName != null'> AND c.candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "ORDER BY" +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "vote.gmt_create DESC " +
            "LIMIT #{start}, #{length}" +
            "</script>")
    List<VoteDTO> listLogVote(
            @Param("projectName") String projectName,
            @Param("userName") String userName,
            @Param("candidateName") String candidateName,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    @Select("<script>Select count(*) from vote " +
            "JOIN user ON user.user_id = vote.user_id " +
            "JOIN candidate ON candidate.candidate_id = vote.candidate_id " +
            "JOIN project ON project.project_id = vote.project_id " +
            "where true " +
            "<if test = 'projectName != null'> AND project.project_name LIKE CONCAT('%', #{projectName}, '%') </if>" +
            "<if test = 'userName != null'> AND user.user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'candidateName != null'> AND candidate.candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if> " +
            "</script>")
    Long countLogVote(
            @Param("projectName") String projectName,
            @Param("userName") String userName,
            @Param("candidateName") String candidateName) throws Exception;


    @Select("<script>Select *, vote.vote_id AS `key`, u.user_name AS `userName`, " +
            "c.candidate_name AS `candidateName`, p.project_name AS `projectName` from vote " +
            "JOIN user u ON u.user_id = vote.user_id " +
            "JOIN candidate c ON c.candidate_id = vote.candidate_id " +
            "JOIN project p ON p.project_id = vote.project_id " +
            "where true " +
            "<if test = 'projectId != null'> AND p.project_id LIKE CONCAT('%', #{projectId}, '%') </if>" +
            "<if test = 'userName != null'> AND u.user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'candidateName != null'> AND c.candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "ORDER BY" +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "vote_id " +
            "LIMIT #{start}, #{length}" +
            "</script>")
    List<VoteDTO> list4Table(
            @Param("projectId") Long projectId,
            @Param("userName") String userName,
            @Param("candidateName") String candidateName,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    @Select("<script>Select count(*) from vote " +
            "JOIN user ON user.user_id = vote.user_id " +
            "JOIN candidate ON candidate.candidate_id = vote.candidate_id " +
            "JOIN project ON project.project_id = vote.project_id " +
            "where true " +
            "<if test = 'projectId != null'> AND project.project_id LIKE CONCAT('%', #{projectId}, '%') </if>" +
            "<if test = 'userName != null'> AND user.user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'candidateName != null'> AND candidate.candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if> " +
            "</script>")
    Long count4Table(
            @Param("projectId") Long projectId,
            @Param("userName") String userName,
            @Param("candidateName") String candidateName) throws Exception;


    @Select("<script>SELECT count(*) FROM user_project up " +
            "JOIN user ON user.user_id = up.user_id " +
            "JOIN project ON project.project_id = up.project_id " +
            "WHERE TRUE " +
            "</script>")
    Long countVoted() throws Exception;

    @Select("<script>SELECT * FROM user_project " +
            "WHERE TRUE </script>")
    List<UPDTO> queryVoted(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Insert("INSERT INTO vote " +
            "(user_id, project_id, candidate_id, sub_level, work_level, agree, gmt_create) " +
            "VALUES(#{entity.userId}, #{entity.projectId}, #{entity.candidateId}, #{entity.subLevel}, " +
            "#{entity.workLevel}, #{entity.agree}, #{entity.gmtCreate})")
    @Options(useGeneratedKeys = true, keyProperty = "voteId", keyColumn = "vote_id")
    Integer add(@Param("entity") VoteEntity entity) throws Exception;

    @Select("SELECT *, vote_id AS `key` from vote " +
            "JOIN candidate c on c.candidate_id = vote.candidate_id " +
            "JOIN project p on p.project_id  = vote.project_id " +
            "WHERE vote.project_id = #{projectId} " +
            "AND vote.candidate_id = #{candidateId}")
    List<VoteDTO> listVote(
            @Param("candidateId") Long candidateId,
            @Param("projectId")Long projectId) throws Exception;

    @Select("<script> Select count(*) from vote " +
            "where candidate_id = #{candidateId} " +
            "and project_id = #{projectId} " +
            "</script>")
    Integer countPoint(@Param("candidateId") Long candidateId,
                       @Param("projectId")Long projectId) throws Exception;

    @Select("<script> Select count(*) from candidate " +
            "where true " +
            "order by candidate_id " +
            "</script>")
    Long countCandidate() throws Exception;

    @Update("UPDATE user_project " +
            "SET voted = 1 " +
            "WHERE user_id = #{entity.userId} AND project_id = #{entity.projectId}")
    Integer updateUP(@Param("entity") VoteEntity voteEntity) throws Exception;

    @Select("<script> SELECT *, c.candidate_id AS `key`, sub_level, work_level, agree FROM candidate c, vote v " +
            "WHERE TRUE " +
            "AND v.candidate_id = c.candidate_id " +
            "<if test = 'userId != null'> AND v.user_id = #{userId} </if> " +
            "<if test = 'projectId != null'> AND v.project_id = #{projectId} </if> " +
            "</script>")
    List<CandidateDTO> queryVoteResult(
            @Param("userId") Long userId,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length
    ) throws Exception;

    @Select("<script> SELECT count(*) FROM vote " +
            "WHERE TRUE " +
            "<if test = 'userId != null'> AND user_id = #{userId} </if>" +
            "<if test = 'projectId != null'> AND project_id = #{projectId} </if>" +
            "</script>")
    Long countVoteResult(
            @Param("userId") Long userId,
            @Param("projectId") Long projectId
    ) throws Exception;

}
